package utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileInputStream;
import java.io.InputStream;

public class ExcelToString {

    public static void main(String[] args) {

        try {
            InputStream inputStream = new FileInputStream("C:\\Users\\iplas\\Desktop/test.xlsx");
            //构建Workbook对象
            Workbook workbook = WorkbookFactory.create(inputStream);
            //获取第一张Sheet表
            Sheet sheet = workbook.getSheetAt(0);
            //获取需要读取内容的开始行标
            int dataRow = 0;
            //获取需要读取内容的结束行标
            int endRow = sheet.getLastRowNum() +1;
            StringBuffer stringBuffer = new StringBuffer();

            //分裂合并单元格
            int sheetMergeCount = sheet.getNumMergedRegions();
            for (int a = 0; a < sheetMergeCount; a++) {
                CellRangeAddress ca = sheet.getMergedRegion(a);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();

                Row fRow = sheet.getRow(firstRow);
                Cell fCell = fRow.getCell(firstColumn);
                String newValue = fCell.toString();
                System.out.println(newValue);
                for (int i = firstRow; i <= lastRow ; i++) {
                    for (int j = firstColumn; j <= lastColumn ; j++) {
                        Row r = sheet.getRow(i);
                        Cell c = r.getCell(j);
                        c.setCellValue(newValue);
                    }
                }
            }

            if (endRow > 1) {
                for (int i = dataRow; i < endRow; i++) {
                    //获取需要读取内容的行
                    Row row = sheet.getRow(i);
                    if (row != null) {
                        //读取Sheet的50列
                        //遍历并获取需要读取的单元格
                        for(int j=0;j<50;j++){
                            Cell cell = row.getCell(j);
                            if(cell !=null && !"".equals(cell)){
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                stringBuffer.append(cell.toString().replaceAll("\n","").replaceAll("\t","") + " ");
                            }
                        }
                        stringBuffer.append("\n");
                    }
                }
            }

            System.out.println(stringBuffer.toString());


        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
